package DatabaseManager;

/**
 * Database Management Class
 */

import java.sql.*;

public class TempMSGBox {
	
	/**
	 * Properties
	 */
	private Connection con;
	private PreparedStatement preparedStmt;
	private ResultSet resultSet;
	
	private String url;
	private String id;
	private String pw;
	
	
	/**
	 * Methods
	 */
	public TempMSGBox(){
		con = null;
		preparedStmt = null;
		resultSet = null;
		url = "jdbc:mysql://127.0.0.1:3306/TempMSGBox"; //jdbc:mysql://url:port/'DBname'
		id = "root";
		pw = "wlqwndrydbr";
	}
	/**
	 * Connect DB
	 */
	public void ConnectDB(){
		
		try {
			Class.forName("com.mysql.jdbc.Driver"); // JDBC ����̹��� Ŭ���� �̸�
													// ������ ���� �����ͺ��̽��� URI�� ���� - DB��:test
													// �޼ҵ� ȣ�� ("DB�� URI", "DB ���̵�", "DB ���")
			con = DriverManager.getConnection(url,id,pw);
			System.out.println("DB ���� Ȯ��");
			//conn.close(); // ������ ���� �޼ҵ�
		}
		catch (ClassNotFoundException cnte) {
			System.out.println("�ش� Ŭ������ ã�� �� ����ϴ�" + cnte.getMessage() );
		}
		catch (SQLException se) {
			System.out.println(se.getMessage());
		}
	}
	
	public void CloseConnectionDB() throws SQLException{
		con.close();
	}
	
	public void StoreMessage(String[] mmArray, String[] toArray, String[] ccArray, String[] bccArray){
		
		String sql = null;
		
		/**
		 * Insert into Message_Info Table
		 */
		try{
			sql = "select * from Message_Info where MSGID = (?)";
			preparedStmt = con.prepareStatement(sql);
			preparedStmt.setString(1, mmArray[0]);
			resultSet = preparedStmt.executeQuery();
			System.out.println("Select Success");
			
			
			if(!resultSet.next()) 	//Query �� ��� ���� ���� ������ 
			{						//���� MSG-ID�� ����� ������ ����.
				try{
					sql = "insert into Message_Info values(?,?,?,?,?,?)";
					preparedStmt = con.prepareStatement(sql);
					preparedStmt.setString(1, mmArray[0]);	//MSGID
					preparedStmt.setString(2, mmArray[1]);	//Subject
					preparedStmt.setString(3, mmArray[2]);	//ContentLocation
					preparedStmt.setString(4, mmArray[3]);	//Date
					preparedStmt.setString(5, mmArray[4]);	//Expiry
					preparedStmt.setString(6, mmArray[5]);	//Delivery Time
					preparedStmt.executeUpdate();
					System.out.println("Query1 Success");
				}catch(SQLException se){
					System.out.println("Query 1" + se.getMessage());
				}
			}
		}catch (SQLException se){
			System.out.println(se.getMessage());
		}
		
		sql = null;
		preparedStmt = null;
		
		/**
		 * Insert into SenderReceiver_Info Table
		 */
		try{
			for(int i=0 ; i < toArray.length ; i++)
			{
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]);	//MSGID
				preparedStmt.setString(2, mmArray[6]);	//Sender
				preparedStmt.setString(3, toArray[i]);	//Receiver
				preparedStmt.setString(4, mmArray[8]);	//SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]);	//ReceiverDelivery Allowed
				preparedStmt.setString(6, mmArray[10]);	//Delivery State
				preparedStmt.setString(7, mmArray[11]);	//SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]);	//ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]);	//ReadState
				preparedStmt.setString(10, "To");		//ReceiverState
				preparedStmt.executeUpdate();
				System.out.println("Query2 Success");
			}
		}catch (SQLException se){
			System.out.println("Query2 to" + se.getMessage());
//			this.UpdateMMBox(mmArray);
		}
		
		try{
			for(int i=0 ; i < ccArray.length ; i++)
			{
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]);	//MSGID
				preparedStmt.setString(2, mmArray[6]);	//Sender
				preparedStmt.setString(3, ccArray[i]);	//Receiver
				preparedStmt.setString(4, mmArray[8]);	//SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]);	//ReceiverDelivery Allowed
				preparedStmt.setString(6, mmArray[10]);	//Delivery State
				preparedStmt.setString(7, mmArray[11]);	//SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]);	//ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]);	//ReadState
				preparedStmt.setString(10, "Cc");		//ReceiverState
				preparedStmt.executeUpdate();
				System.out.println("Query2 Success");
			}
		}catch (SQLException se){
			System.out.println("Query2 cc" + se.getMessage());
		}
		
		try{
			for(int i=0 ; i < bccArray.length ; i++)
			{
				sql = "insert into SenderReceiver_Info values(?,?,?,?,?,?,?,?,?,?)";
				preparedStmt = con.prepareStatement(sql);
				preparedStmt.setString(1, mmArray[0]);	//MSGID
				preparedStmt.setString(2, mmArray[6]);	//Sender
				preparedStmt.setString(3, bccArray[i]);	//Receiver
				preparedStmt.setString(4, mmArray[8]);	//SenderDeliveryAllowed
				preparedStmt.setString(5, mmArray[9]);	//ReceiverDelivery Allowed
				preparedStmt.setString(6, mmArray[10]);	//Delivery State
				preparedStmt.setString(7, mmArray[11]);	//SenderReadAllowed
				preparedStmt.setString(8, mmArray[12]);	//ReceiverReadAllowed
				preparedStmt.setString(9, mmArray[13]);	//ReadState
				preparedStmt.setString(10, "Bcc");		//ReceiverState
				preparedStmt.executeUpdate();
				System.out.println("Query2 Success");
			}
		}catch (SQLException se){
			System.out.println("Query2 bcc" + se.getMessage());
		}
	}
	
	/**
	 * Update the Database (report, readstate)
	 */
	public void UpdateMMBox(String[] mmArray)
	{
		String sql = null;
		preparedStmt = null;
		try{
			System.out.println("here is UpdateMMBox");
			sql = "update SenderReceiver_Info set SenderDeliveryAllowed = '" + mmArray[8] +"', "
				+ "ReceiverDeliveryAllowed = '" + mmArray[9] + "', "
				+ "DeliveryState = '" + mmArray[10] + "', "
				+ "SenderReadAllowed = '" + mmArray[11] + "', "
				+ "ReceiverReadAllowed = '" + mmArray[12] + "', "
				+ "ReadState = '" + mmArray[13] + "' " + " where "
				+ "MSGID = '" + mmArray[0] + "' and "
				+ "Sender = '" + mmArray[6] + "' and "
				+ "Receiver = '" + mmArray[7] + "'";
			System.out.println("Query is " + sql);
			
			preparedStmt = con.prepareStatement(sql);
			preparedStmt.executeUpdate();
			System.out.println("Update Success");
			
		}catch (SQLException se){
			System.out.println("UpdateMMBox" + se.getMessage());
		}
	}
	
	/**
	 * Move Data TempMSGBox to MMBox //need update. -->when Forward
	 * @throws SQLException 
	 */
	public void StoreToMMBOX() throws SQLException{
		MMBoxManager mmBoxManager = new MMBoxManager();
		mmBoxManager.ConnectDB();
		
		String sql = "select * ";
		con.prepareStatement(sql);	
		
		
		
	}
	
	public boolean isExistMsgId(String msgID)throws SQLException{
		String sql = null;
		preparedStmt = null;
		resultSet = null;

		sql = "select * from Message_Info where MSGID = ?";
		preparedStmt = con.prepareStatement(sql);
		preparedStmt.setString(1, msgID);
		resultSet = preparedStmt.executeQuery();
		
		if(resultSet.next())
			return true;
		else
			return false;
	}
	

}
